package controllers;

import java.io.ByteArrayInputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import lib.UtilsInput;
import models.Reporte12;

import com.google.gson.Gson;
import com.google.gson.JsonObject;
import com.google.gson.JsonParser;

import play.db.DB;
import play.mvc.Controller;

public class Reporte_12 extends Controller{
	
	public static void Reporte_12(){
		if(Security.isAuthenticated() && Security.havePermission())
			render();
		else
			Application.login();
	}
	
	public static void getReporte12(String mes,String anio){
		String query = "select PRO_SERVICIO,CLI_NOMBRES,PRO_MONTO from SIGPC_PROYECTO sigpro join SIGPC_CLIENTE sigcli on sigpro.CLI_ID = sigcli.CLI_ID WHERE PRO_AVANCE = '100' order by PRO_MONTO desc ";
		String str = null;
		Gson gson = new Gson();
    	Connection conn = DB.getConnection();
    	
    	//Lista de objetos 
    	List<Reporte12> listReporte12 = new ArrayList<Reporte12>();
    	System.out.println(mes);
    	
    	try {
    		if (mes != null &&  anio != null)
    		{   			
    			if (!mes.equalsIgnoreCase("0") &&  !anio.equalsIgnoreCase("0"))
        			query = "select PRO_SERVICIO,CLI_NOMBRES,PRO_MONTO from SIGPC_PROYECTO sigpro join SIGPC_CLIENTE sigcli on sigpro.CLI_ID = sigcli.CLI_ID WHERE PRO_AVANCE = '100' and extract(year from pro_fecha_ini) = "+ anio +" and extract(month from pro_fecha_ini) = "+ mes +" order by PRO_MONTO desc ";
    	
    		}	
    			
    		
        	ResultSet rs = conn.createStatement().executeQuery(query);
        	
        	 while (rs.next()) {
        		 Reporte12 reporte12 = new Reporte12(rs.getString("CLI_NOMBRES"),rs.getString("PRO_SERVICIO"),rs.getFloat("PRO_MONTO"));
        		 listReporte12.add(reporte12);
        		 
        	        	
        	 }
    		
        	 str = "{\"success\":true ,\"records\":" + listReporte12.size()
     				+ ", \"total\":1 , \"page\":1, \"rows\":" + gson.toJson(listReporte12)
     				+ "}";

     		renderJSON(str);
     		
        	        	
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	//JASPER REPORT XLS
	public static void  download_report(String mes1, String mes2, String anio, String formato) {
		Map reportParams = new HashMap();
	    
		if (formato != null)
		{
			reportParams.put("mes1", mes1);
			reportParams.put("mes2", mes2);
			reportParams.put("anio",  anio);
				  		  
			if (formato.equalsIgnoreCase("html"))
				renderHtml( UtilsInput.toString((ByteArrayInputStream) lib.BaseJasperReport.generateReport("reporte_12", reportParams,formato)));
			else
				renderBinary(lib.BaseJasperReport.generateReport("reporte_12", reportParams,formato), "reporte_12."+ formato);
		}
	}

}
